# -*- coding:utf-8 -*-
import MySQLdb
import datetime
from pyecharts import Line
from project_zczh.project_1.data_gangcai import now_time
from . import project
from flask import render_template
from project_zczh.tool.common import login_check
import calendar
from decimal import Decimal
@project.route('/table/shuini')
@login_check
def data_shuini():
    global key
    key = '代号:P.O'
    mysql_db= MySQLdb.connect(
        port=3306,
        host='192.168.1.108',
        db='zczh_data',
        user='root',
        passwd='zczh',
        charset='utf8'
    )
    global cur
    cur=mysql_db.cursor()
    now_time_point=datetime.datetime.now()
    year=now_time_point.year
    month=now_time_point.month
    date=now_time_point.day
    data_tuple=calendar.monthrange(year,month)
    if date == data_tuple[1]:
        bar=bar_month()
        table_title, rows_data = table_data_month()
    else:
        table_title, rows_data = table_data()
        bar=bar_()
    mysql_db.close()
    data="全国水泥价格实时数据"
    return render_template('data_shuini.html',data=data,table_title=table_title,rows_data=rows_data,bar=bar.render_embed())

def table_data_month():
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    befor_year_1 = str(befor_year) + '-12-31'
    befor_year_2 = str(befor_year) + '-01-01'
    cur_year_1 = str(cur_year) + '-12-31'
    cur_year_2 = str(cur_year) + '-01-01'
    sql = """select avg(price),date_format(pub_time,%s),format,region from shuini_t where pub_time<=%s and pub_time>=%s and format = %s and region in ('安徽','浙江','上海','山东','江苏','福建') group by date_format(pub_time,%s);"""
    cur.execute(sql, ['%Y-%m', befor_year_1, befor_year_2, key, '%Y-%m'])
    year_17_data = cur.fetchall()
    sql = """select avg(price),date_format(pub_time,%s),format,region from shuini_t where pub_time<=%s and pub_time>=%s and format = %s and region in ('安徽','浙江','上海','山东','江苏','福建') group by date_format(pub_time,%s);"""
    cur.execute(sql, ['%Y-%m', cur_year_1, cur_year_2, key, '%Y-%m'])
    year_18_data = cur.fetchall()
    month_show = ['{}月份'.format(i) for i in range(1, 13)]
    table_title = ['说明', '型号'] + month_show
    rows_data_2017 = ['%s月平均值' % befor_year, 'P.O']
    rows_data_2018 = ['%s月平均值' % cur_year, 'P.O']
    for i in year_17_data:
        rows_data_2017.append(eval(str(Decimal(i[0]).quantize(Decimal('0.00')))))
    for k in year_18_data:
        rows_data_2018.append(eval(str(Decimal(k[0]).quantize(Decimal('0.00')))))
    if len(rows_data_2017) != len(rows_data_2018):
        a = len(rows_data_2017) - len(rows_data_2018)
        for _ in range(a):
            rows_data_2018.append('-')
    rows_data = list()
    rows_data.append(rows_data_2017)
    rows_data.append(rows_data_2018)
    return table_title, rows_data

def table_data():
    now=datetime.datetime.now().strftime("%Y-%m-%d")
    time_delta=datetime.timedelta(31)
    after=(datetime.datetime.now()-time_delta).strftime("%Y-%m-%d")
    sql="""select pub_time,avg(price),region,format from shuini_t where pub_time<=%s and pub_time>=%s and format = %s group by region,pub_time;"""
    try:
        ret=cur.execute(sql,[now,after,key])
    except MySQLdb.MySQLError as f:
        print f
    if ret == 0:
        return ret
    data=cur.fetchall()
    area=['上海','山东','江苏','浙江','安徽','福建']
    rows_data=[[addr,key] for addr in area]
    table_title=['区域','规格']
    table_title_time=list()
    for i in data:
        table_title_time.append(str(i[0])[:10])
        for k in rows_data:
            if i[2] == k[0]:
                k.append(int(i[1]))
    check_data=[]
    rows=[]
    for i in rows_data:
        check_data.append(len(i))
    for i in rows_data:
        if len(i)==max(check_data):
            rows.append(i)
    rows_data=rows
    table_title_time=list(set(table_title_time))
    table_title_time.sort()
    table_title+=table_title_time
    return table_title,rows_data

def get_data():
    now,delta_now=now_time(30)
    sql="""select pub_time,avg(price),format from shuini_t where pub_time<=%s and pub_time>=%s and format = %s group by pub_time;"""
    cur.execute(sql,[now,delta_now,key])
    data=cur.fetchall()
    data_time=[]
    data_prict=[]
    for i in data:
        data_time.append(str(i[0])[:10])
        data_prict.append(int(i[1]))
    return data_time,data_prict
def bar_():
    data_time,data_price=get_data()
    print data_time
    print data_price
    attr = data_time
    bar = Line("全国-%s"%'P.O')
    bar.width=900
    bar.add("月走势-%s"%'P.O',
            attr,
            data_price,
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=True,
            mark_point=['max'])
    return bar
def bar_month():
    month_show = ['{}月份'.format(i) for i in range(1, 13)]
    data_tuple=get_data_month()
    attr = month_show
    bar = Line("全国-%s"%'P.O')
    bar.width=900
    bar.add("2018年度走势-%s" % 'P.O',
            attr,
            data_tuple[1][1][2:],
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=True,
            # mark_point=['max']
            )
    bar.add("2017年度走势-%s"%'P.O',
            attr,
            data_tuple[1][0][2:],
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=True,
            # mark_point=['max']
            )
    return bar

def get_data_month():
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    datetime_num = 30
    befor_year_1 = str(befor_year) + '-12-31'
    befor_year_2 = str(befor_year) + '-01-01'
    cur_year_1 = str(cur_year) + '-12-31'
    cur_year_2 = str(cur_year) + '-01-01'
    if datetime_num == 30:
        sql = """select avg(price),date_format(pub_time,%s),format,region from shuini_t where pub_time<=%s and  pub_time>=%s and region in ('安徽','浙江','上海','山东','江苏','福建') and format = %s group by date_format(pub_time,%s);"""
        cur.execute(sql, ['%Y-%m', befor_year_1, befor_year_2, key, '%Y-%m'])
        year_17_data = cur.fetchall()
        sql = """select avg(price),date_format(pub_time,%s),format,region from shuini_t where pub_time<=%s and pub_time>=%s  and region in ('安徽','浙江','上海','山东','江苏','福建') and format = %s group by date_format(pub_time,%s);"""
        cur.execute(sql, ['%Y-%m', cur_year_1, cur_year_2, key, '%Y-%m'])
        year_18_data = cur.fetchall()
        month_show = ['{}月份'.format(i) for i in range(1, 13)]
        table_title = ['说明', '型号'] + month_show
        rows_data_2017 = ['2017月平均值', 'P.O']
        rows_data_2018 = ['2017月平均值', 'P.O']
        for i in year_17_data:
            rows_data_2017.append(eval(str(Decimal(i[0]).quantize(Decimal('0.00')))))
        for k in year_18_data:
            rows_data_2018.append(eval(str(Decimal(k[0]).quantize(Decimal('0.00')))))
        rows_data=list()
        rows_data.append(rows_data_2017)
        rows_data.append(rows_data_2018)
        return table_title, rows_data